<?php
class etat_financier_mdl extends CI_Model
{

	public function get_all()
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
*
FROM
budget
WHERE
budget.annee = $year

 ");

return $query->result();
     

	
	}
	
	public function calcule_chapitre($num_ch)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
bc_produits.quantite,
bc_produits.bc_produit_pu
FROM
engagements
INNER JOIN bcs ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
INNER JOIN articles ON engagements.id_article_fk = articles.id_article AND '' = ''
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
WHERE
chapitres.id_chapitre = '$num_ch' AND
engagements.annee = '$year'

 ");

return $query->result();
   

	
	}
	
	public function calcule_chapitre_payer($num_ch)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
bc_produits.quantite,
bc_produits.bc_produit_pu
FROM
engagements
INNER JOIN bcs ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
INNER JOIN articles ON engagements.id_article_fk = articles.id_article AND '' = ''
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
WHERE
chapitres.id_chapitre = '$num_ch' AND
engagements.annee = '$year' AND
bcs.id_mondat_fk IS NOT NULL


 ");

return $query->result();
   

	
	}

    public function calcule_chapitre_convention($num_ch)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
convention_produits.id_produit_fk,
convention_produits.quantite,
convention_produits.pu_convention_produit
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article AND ''= ''
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
INNER JOIN conventions ON conventions.id_article_fk = articles.id_article AND conventions.id_engagement_fk = engagements.id_engagement
INNER JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
WHERE
chapitres.id_chapitre = '$num_ch' AND
engagements.annee = '$year'

 ");

return $query->result();
   

	
	}
	
	 public function calcule_chapitre_convention_payer($num_ch)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
convention_produits.id_produit_fk,
convention_produits.quantite,
convention_produits.pu_convention_produit,
conventions.id_mondat_fk
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article AND ''= ''
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
INNER JOIN conventions ON conventions.id_article_fk = articles.id_article AND conventions.id_engagement_fk = engagements.id_engagement
INNER JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
WHERE
chapitres.id_chapitre = '$num_ch' AND
engagements.annee = '$year' AND
conventions.id_mondat_fk IS NOT NULL


 ");

return $query->result();
   

	
	}
	
		public function get_budget_chapitre($num_ch)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 $chapitre='ch_'.$num_ch;	 
	$query = $this->db->query("SELECT
budget.$chapitre
FROM
budget
WHERE
budget.annee = $year


 ");

return $query->result();
   

	
	}
	
		public function calcule_article($num_ch,$code_art)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 $chapitre='ch_'.$num_ch;	 
	$query = $this->db->query("SELECT
articles.nom,
bc_produits.quantite,
bc_produits.bc_produit_pu,
articles.`code`
FROM
articles
INNER JOIN engagements ON engagements.id_article_fk = articles.id_article
INNER JOIN bcs ON bcs.id_engagement_fk = engagements.id_engagement
INNER JOIN bc_produits ON bc_produits.id_bc_fk = bcs.id_bc
WHERE
articles.id_chapitre_fk = $num_ch AND
articles.`code` = $code_art AND
bcs.annee = $year

 ");

return $query->result();
   

	
	}

       public function calcule_article_payer($num_ch,$code_art)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 $chapitre='ch_'.$num_ch;	 
	$query = $this->db->query("SELECT
articles.nom,
articles.`code`,
convention_produits.id_produit_fk,
convention_produits.quantite,
convention_produits.pu_convention_produit
FROM
articles
INNER JOIN engagements ON engagements.id_article_fk = articles.id_article
INNER JOIN conventions ON conventions.id_article_fk = articles.id_article AND conventions.id_engagement_fk = engagements.id_engagement
INNER JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
WHERE
articles.id_chapitre_fk = $num_ch AND
articles.`code` = $code_art and
conventions.id_mondat_fk IS NOT NULL and 
conventions.id_mondat_fk = $year
 ");

return $query->result();
   

	
	}
	
		public function calcule_article_convention($num_ch,$code_art)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 $chapitre='ch_'.$num_ch;	 
	$query = $this->db->query("SELECT
articles.nom,
articles.`code`,
convention_produits.id_produit_fk,
convention_produits.quantite,
convention_produits.pu_convention_produit
FROM
articles
INNER JOIN engagements ON engagements.id_article_fk = articles.id_article
INNER JOIN conventions ON conventions.id_article_fk = articles.id_article AND conventions.id_engagement_fk = engagements.id_engagement
INNER JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
WHERE
articles.id_chapitre_fk = $num_ch AND
articles.`code` = $code_art AND
conventions.annee=$year
 ");

return $query->result();
   

	
	}
	
	public function calcule_article_convention_payer($num_ch,$code_art)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
 $chapitre='ch_'.$num_ch;	 
	$query = $this->db->query("SELECT
articles.nom,
articles.`code`,
convention_produits.id_produit_fk,
convention_produits.quantite,
convention_produits.pu_convention_produit
FROM
articles
INNER JOIN engagements ON engagements.id_article_fk = articles.id_article
INNER JOIN conventions ON conventions.id_article_fk = articles.id_article AND conventions.id_engagement_fk = engagements.id_engagement
INNER JOIN convention_produits ON convention_produits.id_convention_fk = conventions.id_convetion
WHERE
articles.id_chapitre_fk = $num_ch AND
articles.`code` = $code_art and
conventions.id_mondat_fk IS NOT NULL AND
conventions.annee=$year
 ");

return $query->result();
   

	
	}
	
	public function nouveau_budget_init($ch_11,$ch_12,$ch_13,$ch_14,$ch_15,$ch_16,$ch_17,$ch_18,$ch_19,$budget_init)
	{
		

 $this->load->library('session');
 $year=$this->session->userdata('annee');
    $query1 = $this->db->query("SELECT budget.id
FROM budget
WHERE budget.annee = $year AND
budget.initiale = 0");
 $cont=$query1->num_rows();
 
 if($cont==0){
	$query = $this->db->query("INSERT INTO `budget` (`montant_globale`, `ch_11`, `ch_12`, `ch_13`, `ch_14`, `ch_15`, `ch_16`, `ch_17`, `ch_18`, `ch_19`, `annee`) VALUES ('$budget_init', '$ch_11', '$ch_12', '$ch_13', '$ch_14', '$ch_15', '$ch_16', '$ch_17', '$ch_18', '$ch_19', '$year')
 ");
}
//return $query->result();
   

	
	}
	
	public function nouveau_budget_supl($ch_11,$ch_12,$ch_13,$ch_14,$ch_15,$ch_16,$ch_17,$ch_18,$ch_19,$budget_sup)
	{
		

 $this->load->library('session');
 $year=$this->session->userdata('annee');
    $query1 = $this->db->query("SELECT budget.id
FROM budget
WHERE budget.annee = $year AND
budget.initiale = 1");
 $cont=$query1->num_rows();
 
 if($cont==0){
	$query = $this->db->query("INSERT INTO `budget` (`montant_globale`, `ch_11`, `ch_12`, `ch_13`, `ch_14`, `ch_15`, `ch_16`, `ch_17`, `ch_18`, `ch_19`, `annee`, `initiale`) VALUES ('$budget_sup', '$ch_11', '$ch_12', '$ch_13', '$ch_14', '$ch_15', '$ch_16', '$ch_17', '$ch_18', '$ch_19', '$year','1')
 ");
}
//return $query->result();
   

	
	}
	
	public function modifier_budget_init($ch_11,$ch_12,$ch_13,$ch_14,$ch_15,$ch_16,$ch_17,$ch_18,$ch_19,$budget_init,$id)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
  
	$query = $this->db->query("UPDATE `budget` SET `montant_globale`='$budget_init', `ch_11`='$ch_11', `ch_12`='$ch_12', `ch_13`='$ch_13', `ch_14`='$ch_14', `ch_15`='$ch_15', `ch_16`='$ch_16', `ch_17`='$ch_17', `ch_18`='$ch_18', `ch_19`='$ch_19', `annee`='$year' WHERE (`id`='$id') ");

//return $query->result();
   

	
	}
   
    public function modifier_budget_supl($ch_11,$ch_12,$ch_13,$ch_14,$ch_15,$ch_16,$ch_17,$ch_18,$ch_19,$budget_init,$id)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
  
	$query = $this->db->query("UPDATE `budget` SET `montant_globale`='$budget_init', `ch_11`='$ch_11', `ch_12`='$ch_12', `ch_13`='$ch_13', `ch_14`='$ch_14', `ch_15`='$ch_15', `ch_16`='$ch_16', `ch_17`='$ch_17', `ch_18`='$ch_18', `ch_19`='$ch_19', `annee`='$year' WHERE (`id`='$id') ");

//return $query->result();
   

	
	}
    
	public function nouveau_budget_init_chapitre($tab,$num_ch)
	{
		

 $this->load->library('session');
 $year=$this->session->userdata('annee');
    $query1 = $this->db->query("SELECT articles.id_article
FROM articles
WHERE articles.id_chapitre_fk = $num_ch
");
 $id_art=$query1->result();
 
 for($j=0;$j<sizeof($id_art);$j++){
 	$id=$id_art[$j]->id_article;
	$qq=$this->db->query("SELECT budget_art.id_budget_art,budget_art.solde
FROM budget_art
WHERE budget_art.annee = $year AND budget_art.id_art_fk = $id AND budget_art.init = 0
");
$nn=$qq->num_rows();
if($nn==0){
	
	$query = $this->db->query("INSERT INTO `budget_art` (`id_art_fk`, `annee`, `solde`) VALUES ('$id', '$year', '$tab[$j]')
 ");
 
 $query = $this->db->query("
INSERT INTO `engagements` ( `numero`, `nature`, `id_article_fk`, `annee` `montant_engagement`) VALUES ( '1', '2', '$id' ,'$year','$tab[$j]')
 ");
 }
}
//return $query->result();
   

	
	}
	
	public function modifier_budget_init_chapitre($tab,$num_ch,$date)
	{
		

 $this->load->library('session');
 $year=$this->session->userdata('annee');
    $query1 = $this->db->query("SELECT articles.id_article
FROM articles
WHERE articles.id_chapitre_fk = $num_ch
");
 $id_art=$query1->result();
 
 for($j=0;$j<sizeof($id_art);$j++){
 	$id=$id_art[$j]->id_article;
	
    $query2 = $this->db->query("SELECT
budget_art.id_budget_art FROM budget_art
WHERE budget_art.id_art_fk = $id AND budget_art.annee = $year AND budget_art.init ='0'
 ");
 if($query2->result()){
 	
	$query = $this->db->query("UPDATE `budget_art` SET `solde`='$tab[$j]' WHERE (`id_art_fk`='$id' and `annee`='$year' and `init`='0')
 ");
 
 
 
 $query11 = $this->db->query("UPDATE `engagements` SET `montant_engagement`='$tab[$j]' WHERE (`numero`='1' and `annee`='$year' and `id_article_fk`='$id')
 ");
 }
 else {
 	$query = $this->db->query("INSERT INTO `budget_art` (`id_art_fk`, `annee`, `solde`) VALUES ('$id', '$year', '$tab[$j]')
 ");
 

 
  $query = $this->db->query("
INSERT INTO `engagements` ( `numero`, `nature`, `id_article_fk`, `annee`, `montant_engagement`, `date`) VALUES ( '1', '2', '$id' ,'$year','$tab[$j]','$date')
 ");
 }
}
//return $query->result();
   

	
	}
	
	public function modifier_budget_supl_chapitre($tab,$num_ch)
	{
		

 $this->load->library('session');
 $year=$this->session->userdata('annee');
    $query1 = $this->db->query("SELECT articles.id_article
FROM articles
WHERE articles.id_chapitre_fk = $num_ch
");
 $id_art=$query1->result();
 
 for($j=0;$j<sizeof($id_art);$j++){
 	$id=$id_art[$j]->id_article;
    
	 $query2 = $this->db->query("SELECT
budget_art.id_budget_art FROM budget_art
WHERE budget_art.id_art_fk = $id AND budget_art.annee = $year AND budget_art.init ='1'
 ");
 
  if($query2->result())	$query = $this->db->query("UPDATE `budget_art` SET `solde`='$tab[$j]' WHERE (`id_art_fk`='$id' and `annee`='$year' and `init`='1')
 ");
 else $query = $this->db->query("INSERT INTO `budget_art` (`id_art_fk`, `annee`, `solde`, `init`) VALUES ('$id', '$year', '$tab[$j]', '1')
 ");
}
//return $query->result();
   

	
	}
	
	public function modifier_budget_supl_art($id_art,$montant)
	{
		

 $this->load->library('session');
 $year=$this->session->userdata('annee');
   
 
   $query = $this->db->query("UPDATE `engagements` SET `montant_engagement`='$montant' WHERE (`id_article_fk`='$id' and `annee`='$year' and `nature`='2')
 ");
	
 
	$query = $this->db->query("UPDATE `budget_art` SET `solde`='$montant' WHERE (`id_art_fk`='$id' and `annee`='$year' and `init`='1')
 ");
 
//return $query->result();
   

	
	}
	
	public function ajout_budget_supl_art($id_art,$montant,$date,$num_eng)
	{
		

 $this->load->library('session');
 $year=$this->session->userdata('annee');
   
 
   $query = $this->db->query("INSERT INTO `engagements` (`date`, `numero`, `nature`, `id_article_fk`, `annee`, `montant_engagement`) VALUES ('$date', '$num_eng', '2', '$id_art', '$year', '$montant')
 ");
	
 
	
 
//return $query->result();
   $query = $this->db->query("INSERT INTO `budget_art` (`id_art_fk`, `annee`, `solde`, `init`) VALUES ('$id_art', '$year', '$montant', '1')
 ");

	
	}

    	public function nouveau_budget_supl_chapitre($tab,$num_ch)
	{
		

 $this->load->library('session');
 $year=$this->session->userdata('annee');
    $query1 = $this->db->query("SELECT articles.id_article
FROM articles
WHERE articles.id_chapitre_fk = $num_ch
");
 $id_art=$query1->result();
 
 for($j=0;$j<sizeof($id_art);$j++){
 	$id=$id_art[$j]->id_article;
	$qq=$this->db->query("SELECT budget_art.id_budget_art,budget_art.solde
FROM budget_art
WHERE budget_art.annee = $year AND budget_art.id_art_fk = $id AND budget_art.init = 1
");
$nn=$qq->num_rows();
if($nn==0)
	$query = $this->db->query("INSERT INTO `budget_art` (`id_art_fk`, `annee`, `solde`, `init`) VALUES ('$id', '$year', '$tab[$j]' ,'1')
 ");
}
//return $query->result();
   

	
	}
	
	public function get_articles($num_ch)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
articles.nom,
articles.id_chapitre_fk,
budget_art.annee,
budget_art.solde,
chapitres.nom AS chapitre
FROM
articles
INNER JOIN budget_art ON budget_art.id_art_fk = articles.id_article
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
WHERE
articles.id_chapitre_fk = $num_ch AND
budget_art.annee = $year AND
budget_art.init = 0


 ");

$cont=$query->num_rows();

if($cont==0){
	$query = $this->db->query("SELECT
articles.nom,
articles.id_chapitre_fk,
articles.id_article,
articles.`code`,
chapitres.nom AS chapitre
FROM
articles
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
WHERE
articles.id_chapitre_fk = $num_ch
 ");
}
return $query->result();
     

	
	}

    public function get_budget_ch($num_ch)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');

	$query = $this->db->query("SELECT
budget.$num_ch,
budget.initiale,
budget.annee
FROM
budget
WHERE
budget.annee = $year 
 ");

return $query->result();
     

	
	}
	
	
	public function get_articles_supl($num_ch)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
articles.nom,
articles.id_chapitre_fk,
budget_art.annee,
budget_art.solde,
chapitres.nom AS chapitre
FROM
articles
INNER JOIN budget_art ON budget_art.id_art_fk = articles.id_article
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
WHERE
articles.id_chapitre_fk = $num_ch AND
budget_art.annee = $year AND
budget_art.init = 1


 ");

$cont=$query->num_rows();

if($cont==0){
	$query = $this->db->query("SELECT
articles.nom,
articles.id_chapitre_fk,
articles.id_article,
articles.`code`,
chapitres.nom AS chapitre
FROM
articles
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
WHERE
articles.id_chapitre_fk = $num_ch
 ");
}
return $query->result();
     

	
	}
	
	public function get_articles_solde($num_ch,$init)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT

budget_art.solde
FROM
articles
INNER JOIN budget_art ON budget_art.id_art_fk = articles.id_article
INNER JOIN chapitres ON articles.id_chapitre_fk = chapitres.id_chapitre
WHERE
articles.id_chapitre_fk = $num_ch AND
budget_art.annee = $year AND
budget_art.init = $init


 ");


return $query->result();
     

	
	}

    public function etat_transfert($num_art)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
budget_art.transfert,
budget_art.id_budget_art,
budget_art.solde
FROM
budget_art
WHERE
budget_art.annee = $year AND
budget_art.id_art_fk = $num_art AND
budget_art.init = 0

 ");


return $query->result();
     

	
	}

      public function transfert($id_source,$id_dest,$montant_source,$montant_dest,$compt_source,$compt_dest)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("UPDATE `budget_art` SET `solde`='$montant_source', `transfert`='$compt_source' WHERE (`id_budget_art`='$id_source')
 ");

$query = $this->db->query("UPDATE `budget_art` SET `solde`='$montant_dest', `transfert`='$compt_dest' WHERE (`id_budget_art`='$id_dest')
 ");


     

	
	}
	
	   public function ajout_transfert($numero,$id_source,$id_dest,$montant,$date,$new_montant_source,$new_montant_dest)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("INSERT INTO `transfert` (`article_source`, `article_dest`, `montant`, `year`, `date`, `numero`, `new_montant_source`, `new_montant_dest`) VALUES ('$id_source', '$id_dest', '$montant', '$year', '$date', '$numero','$new_montant_source','$new_montant_dest')
 ");
 
   $query1 = $this->db->query("SELECT
transfert.id_tranfert,
transfert.numero
FROM
transfert
ORDER BY
transfert.id_tranfert DESC
LIMIT 0, 1

 ");

return $query1->result();
 
	}
	
	  public function get_transfert()
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
*
FROM
transfert
WHERE
transfert.`year` = $year

 ");


return $query->result();
     

	
	}

      public function supprimer_transfert($id_transfert,$art_source,$art_dest,$solde_source,$solde_dest,$compt_source,$compt_dest)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("DELETE FROM `transfert` WHERE (`id_tranfert`='$id_transfert')

 ");
 
 $query = $this->db->query("UPDATE `budget_art` SET `solde`='$solde_source', `transfert`='$compt_source' WHERE (`id_art_fk`='$art_source' and `annee`='$year')
 ");
 
 $query = $this->db->query("UPDATE `budget_art` SET `solde`='$solde_dest', `transfert`='$compt_dest' WHERE (`id_art_fk`='$art_dest' and `annee`='233')

 ");


	
	}

      public function get_detaille_transfert($id_transfert)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
transfert.article_source,
transfert.article_dest,
art_source.nom AS art_source,
transfert.id_tranfert,
art_dest.nom AS art_destination,
transfert.montant,
transfert.date,
transfert.numero,
transfert.`year`,
art_dest.num_article AS dest_code_art,
art_source.num_article AS source_art_code,
art_source.id_article AS id_art_source,
art_dest.id_article AS id_art_dest,
transfert.new_montant_source,
transfert.new_montant_dest
FROM
transfert
LEFT JOIN articles AS art_source ON transfert.article_source = art_source.id_article
INNER JOIN articles AS art_dest ON transfert.article_dest = art_dest.id_article
WHERE
transfert.id_tranfert = $id_transfert

 ");


return $query->result();
     

	
	}

    public function existe_numero_transfert($numero)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
transfert.id_tranfert
FROM
transfert
WHERE
transfert.numero = $numero AND
transfert.`year` = $year

 ");


return $query->result();
     

	
	}

     public function get_numero_transfert()
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
transfert.numero
FROM
transfert
ORDER BY
transfert.numero DESC
LIMIT 0, 1


 ");


return $query->result();
     

	
	}

     public function get_etat_budget_art($art)
	{

 $this->load->library('session');
 $year=$this->session->userdata('annee');
		 
	$query = $this->db->query("SELECT
budget_art.solde
FROM
budget_art
WHERE
budget_art.id_art_fk = $art AND
budget_art.annee = $year



 ");


return $query->result();
     

	
	}
}


?>